using System;
using System.Text;
using System.Collections.Generic;
using Microsoft.VisualStudio.TestTools.UnitTesting;

using Crainiate.Data.Persistence;

namespace Crainiate.Data.Persistence.Testing
{
    [TestClass]
    public class QueryTests
    {
        //Initialise test class
        [ClassInitialize()]
        public static void ClassInitialize(TestContext testContext)
        {
            Customer customer = new Customer(Component.Instance.GetContext().CreateCommand());
            customer.Id = 30;
            customer.FirstNames = "Frank";
            customer.CreditLimit = 90;
            customer.CustomerType = CustomerType.Standard;

            customer.Insert();

            customer = new Customer(Component.Instance.GetContext().CreateCommand());
            customer.Id = 31;
            customer.FirstNames = "Bob";
            customer.CreditLimit = 12;
            customer.CustomerType = CustomerType.Reseller;

            customer.Insert();

            Order order = new Order(Component.Instance.GetContext().CreateCommand());
            order.Id = 9;
            order.Insert();

            CustomerOrder co = new CustomerOrder(Component.Instance.GetContext().CreateCommand());
            co.CustomerId = 30;
            co.OrderId = 9;
            co.Insert();

            Product product = new Product(Component.Instance.GetContext().CreateCommand());
            product.Id = 901;
            product.Description = "Cucumbers";
            product.Insert();

            OrderItem item = new OrderItem(Component.Instance.GetContext().CreateCommand());
            item.OrderId = order.Id;
            item.ProductId = product.Id;
            item.Insert();

            product = new Product(Component.Instance.GetContext().CreateCommand());
            product.Id = 902;
            product.Description = "Grapes";
            product.Insert();

            item = new OrderItem(Component.Instance.GetContext().CreateCommand());
            item.OrderId = order.Id;
            item.ProductId = product.Id;
            item.Insert();

            product = new Product(Component.Instance.GetContext().CreateCommand());
            product.Id = 903;
            product.Description = "Pears";
            product.Insert();

            item = new OrderItem(Component.Instance.GetContext().CreateCommand());
            item.OrderId = order.Id;
            item.ProductId = product.Id;
            item.Insert();

            product = new Product(Component.Instance.GetContext().CreateCommand());
            product.Id = 904;
            product.Description = "Oranges";
            product.Insert();

            item = new OrderItem(Component.Instance.GetContext().CreateCommand());
            item.OrderId = order.Id;
            item.ProductId = product.Id;
            item.Insert();

            product = new Product(Component.Instance.GetContext().CreateCommand());
            product.Id = 905;
            product.Description = "Bananas";
            product.Insert();

            item = new OrderItem(Component.Instance.GetContext().CreateCommand());
            item.OrderId = order.Id;
            item.ProductId = product.Id;
            item.Insert();

            //Only tomatos have a weight of 100
            product = new Product(Component.Instance.GetContext().CreateCommand());
            product.Id = 906;
            product.Weight = 100;
            product.Description = "Tomatos";
            product.Insert();

            item = new OrderItem(Component.Instance.GetContext().CreateCommand());
            item.OrderId = order.Id;
            item.ProductId = product.Id;
            item.Insert();

            //Add customer address records
            Address address = new Address(Component.Instance.GetContext().CreateCommand());
            address.Id = 2;
            address.Line1 = "1 Tree Way";
            address.Insert();

            CustomerAddress ca = new CustomerAddress(Component.Instance.GetContext().CreateCommand());
            ca.CustomerId = customer.Id;
            ca.AddressId = address.Id;
            ca.Insert();

            //Add another order
            order = new Order(Component.Instance.GetContext().CreateCommand());
            order.Id = 19;
            order.Insert();

            co = new CustomerOrder(Component.Instance.GetContext().CreateCommand());
            co.CustomerId = 31;
            co.OrderId = 19;
            co.Insert();

            item = new OrderItem(Component.Instance.GetContext().CreateCommand());
            item.OrderId = 19;
            item.ProductId = 905;
            item.Insert();

            item = new OrderItem(Component.Instance.GetContext().CreateCommand());
            item.OrderId = 19;
            item.ProductId = 906;
            item.Insert();
        }

        //Query with object properties
        [TestMethod]
        public void SimpleQuery()
        {
            //Simple query
            Query<Customer> query = new Query<Customer>();
            query.Command = Component.Instance.GetContext().CreateCommand();
            query.Execute();

            foreach (Customer customer in query.Results)
            {
                Assert.IsFalse(customer == null,"Could not iterate query results.");
                customer.Command = query.Command;
                customer.Update();
            }
        }

        //Query with where
        [TestMethod]
        public void SimpleQueryWhere()
        {
            //Simple query
            Query<Customer> query = new Query<Customer>();
            query.Where.Add(new Condition(typeof(Person), "FirstNames", "James"));

            query.Command = Component.Instance.GetContext().CreateCommand();
            query.Execute();

            foreach (Customer customer in query.Results)
            {
                Assert.IsFalse(customer == null, "Could not iterate query results.");
                customer.Command= query.Command;
                customer.Update();
            }
        }

        //Query with where
        [TestMethod]
        public void SimpleQueryWhereLike()
        {
            //Simple query
            Query<Product> query = new Query<Product>();
            query.Where.Add(new Condition(typeof(Product), "Description", "g",Comparison.StartsWith));

            query.Command = Component.Instance.GetContext().CreateCommand();
            query.Execute();

            Assert.IsTrue(query.Results.Count == 1, "Starts with condition failed for simple query.");

            //Simple query
            query = new Query<Product>();
            query.Where.Add(new Condition(typeof(Product), "Description", "range", Comparison.Contains));

            query.Command = Component.Instance.GetContext().CreateCommand();
            query.Execute();

            Assert.IsTrue(query.Results.Count == 1, "Contains condition failed for simple query.");
        }

        //Query with object properties
        [TestMethod]
        public void QueryWithObjectProperties()
        {
            //Simple query
            Query<Order> query = new Query<Order>();
            query.Command = Component.Instance.GetContext().CreateCommand();
            query.Execute();

            int addresses = 0;

            foreach (Order order in query.Results)
            {
                Assert.IsTrue(order != null);
                if (order.ShippingAddress != null && order.ShippingAddress.Line1 != null && order.ShippingAddress.Line1 != string.Empty) addresses++;
            }

            Assert.IsTrue(addresses > 0, "No Object property records (Shipping Address) retrieved.");
        }

        //Simple query
        [TestMethod]
        public void CustomerOrderProducts()
        {
            Query<Customer> customers = new Query<Customer>();
            Query<CustomerOrder> cos = new Query<CustomerOrder>();
            Query<Order> orders = new Query<Order>();
            Query<OrderItem> ois = new Query<OrderItem>();
            Query<Product> products = new Query<Product>();

            customers.Command = Component.Instance.GetContext().CreateCommand();

            customers.Join(cos,"Id","CustomerId");
            cos.Join(orders,"OrderId","Id");
            orders.Join(ois,"Id","OrderId");
            ois.Join(products,"ProductId","Id");

            customers.Execute();

            Assert.IsTrue(customers.Results.Count == 2, "Customers do not total 2");
            Assert.IsTrue(cos.Results.Count == 2, "Customer Orders do not total 2");
            Assert.IsTrue(orders.Results.Count == 2, "Orders do not total 2");
            Assert.IsTrue(ois.Results.Count == 8, "Total Order Items do not total 8");
            Assert.IsTrue(products.Results.Count == 6,"Total products do not total 6");

            Assert.IsTrue(customers.Results[0] is Customer, "Customer result is not a customer object");
            Assert.IsTrue(cos.Results[0] is CustomerOrder, "Customer order result is not a customer order object");
            Assert.IsTrue(orders.Results[0] is Order, "Order result is not an order object");
            Assert.IsTrue(ois.Results[0] is OrderItem, "Order item result is not an order item object");
            Assert.IsTrue(products.Results[0] is Product, "Product result is not a product object");
        }

        //Simple query
        [TestMethod]
        public void CustomerOrderProductsAddress()
        {
            Query<Customer> customers = new Query<Customer>();
            Query<CustomerOrder> co = new Query<CustomerOrder>();
            Query<Order> orders = new Query<Order>();
            Query<OrderItem> oi = new Query<OrderItem>();
            Query<Product> product = new Query<Product>();

            customers.Command = Component.Instance.GetContext().CreateCommand();
            //co.Command = Component.Instance.GetContext().CreateCommand();
            //orders.Command = Component.Instance.GetContext().CreateCommand();
            //oi.Command = Component.Instance.GetContext().CreateCommand();
            //product.Command = Component.Instance.GetContext().CreateCommand();

            Query<CustomerAddress> ca = new Query<CustomerAddress>();
            Query<Address> address = new Query<Address>();

            //ca.Command = Component.Instance.GetContext().CreateCommand();
            //address.Command = Component.Instance.GetContext().CreateCommand();

            customers.Join(co,"Id", "CustomerId");
            co.Join(orders,"OrderId", "Id");
            orders.Join(oi,"Id", "OrderId");
            oi.Join(product,"ProductId", "Id");

            customers.Join(ca,"Id","CustomerId");
            ca.Join(address,"AddressId","Id");

            customers.Execute();

            Assert.IsTrue(ca.Results.Count == 1, "Customer Address objects count not = 1");
            Assert.IsTrue(address.Results.Count == 1, "Address objects count not = 1");

            Assert.IsTrue(ca.Results[0] is CustomerAddress, "Customer Address result is not a CustomerAddress object.");
            Assert.IsTrue(address.Results[0] is Address, "Address result is not an address.");
        }

        //Query with view tests
        [TestMethod]
        public void QueryView()
        {
            //Simple query
            Query<CustomerView> query = new Query<CustomerView>();
            query.Command = Component.Instance.GetContext().CreateCommand();
            query.Execute();

            foreach (CustomerView view in query.Results)
            {
                Assert.IsTrue(view != null);
            }

            //Simple query with condition
            query = new Query<CustomerView>();
            query.Command = Component.Instance.GetContext().CreateCommand();
            query.Where.Add(new Condition(typeof(CustomerView),"FirstNames", "James"));
            query.Execute();

            CustomerView customer = (CustomerView) query.Results[0];
            Assert.IsTrue(customer.FirstNames == "James", "Could not validate conditional view collection");

            //Join a customer view to address
            query = new Query<CustomerView>();
            query.Command = Component.Instance.GetContext().CreateCommand();
            query.Where.Add(new Condition(typeof(CustomerView),"FirstNames", "John"));

            Query<CustomerAddress> ca = new Query<CustomerAddress>();
            Query<Address> address = new Query<Address>();

            //ca.Command = Component.Instance.GetContext().CreateCommand();
            //address.Command = Component.Instance.GetContext().CreateCommand();

            query.Join(ca, "Id", "CustomerId");
            ca.Join(address, "AddressId", "Id");
            query.Execute();

            Assert.IsTrue(customer.FirstNames == "James", "Could not validate conditional joined view collection");
        }

        //Query with view tests
        [TestMethod]
        public void QueryFilter()
        {
            Query<Customer> customers = new Query<Customer>();

            customers.Command = Component.Instance.GetContext().CreateCommand();
            customers.Execute();

            Results<Customer> results = customers.Results as Results<Customer>;
            Column column = new Column(typeof(Person), "FirstNames");
            
            int i = 0;
            foreach (Customer customer in results.FilterBy(column, "James"))
            {
                i++;
            }

            Assert.IsTrue(i == 1, "Filter has returned more or less than one record.");
        }

        //Query with view tests
        [TestMethod]
        public void QueryNullFilter()
        {
            Query<Customer> customers = new Query<Customer>();

            customers.Command = Component.Instance.GetContext().CreateCommand();
            customers.Execute();

            Results<Customer> results = customers.Results as Results<Customer>;
            Column column = new Column(typeof(Person), "FirstNames");

            //Count the number of nulls
            int nulls = 0;
            foreach (Customer customer in results)
            {
                if (customer.FirstNames == null) nulls++;
            }

            int i = 0;
            foreach (Customer customer in results.FilterBy(column, null))
            {
                i++;
            }

            Assert.IsTrue(i == nulls, "Filter has not returned matching nulls");
        }

    }
}
